package com.example.dashuju.repository;

import com.example.dashuju.entity.SalesData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SalesRepository extends JpaRepository<SalesData, Long> {

    // 商品类别销售数据统计
    @Query("SELECT s.category, SUM(s.salesAmount) FROM SalesData s GROUP BY s.category ORDER BY SUM(s.salesAmount) DESC")
    List<Object[]> findCategorySales();

    // 地区销售数据统计
    @Query("SELECT s.region, SUM(s.salesAmount) FROM SalesData s GROUP BY s.region ORDER BY SUM(s.salesAmount) DESC")
    List<Object[]> findRegionSales();

    // 平台销售趋势
    @Query("SELECT s.platform, HOUR(s.salesDate), SUM(s.salesAmount) FROM SalesData s WHERE s.platform IN ('安卓', 'IOS') GROUP BY s.platform, HOUR(s.salesDate) ORDER BY HOUR(s.salesDate)")
    List<Object[]> findPlatformTrend();

    // 重点地区销售业绩
    @Query("SELECT s.region, SUM(s.salesAmount) FROM SalesData s WHERE s.region IN ('浙江', '上海', '江苏', '广东', '北京', '深圳', '安徽', '四川') GROUP BY s.region")
    List<Object[]> findKeyRegionsSales();

    // 核心地区销售占比
    @Query("SELECT s.region, SUM(s.salesAmount) FROM SalesData s WHERE s.region IN ('浙江', '上海', '广东', '北京', '深圳') GROUP BY s.region")
    List<Object[]> findCoreRegionsRatio();

    // 年龄分布
    @Query("SELECT s.ageGroup, COUNT(s) FROM SalesData s WHERE s.ageGroup IS NOT NULL GROUP BY s.ageGroup")
    List<Object[]> findAgeDistribution();

    // 职业分布
    @Query("SELECT s.occupation, COUNT(s) FROM SalesData s WHERE s.occupation IS NOT NULL GROUP BY s.occupation")
    List<Object[]> findOccupationDistribution();

    // 兴趣分布
    @Query("SELECT s.interest, COUNT(s) FROM SalesData s WHERE s.interest IS NOT NULL GROUP BY s.interest")
    List<Object[]> findInterestDistribution();

    // 总销售额统计
    @Query("SELECT YEAR(s.salesDate), SUM(s.salesAmount) FROM SalesData s GROUP BY YEAR(s.salesDate) ORDER BY YEAR(s.salesDate)")
    List<Object[]> findYearlySales();
}